package com.itg.vms.dao.impl;

import com.itg.vms.dao.MoviesDao;
import com.itg.vms.entity.Movies;
import com.itg.vms.entity.User;
import com.itg.vms.util.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MoviesDaoImpl implements MoviesDao {
    QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

    /**
     * 增加视频
     */
    @Override
    public boolean addMovies(Movies movies) {
        String sql = "insert into vms.movie (movie_Picture,movie_ChineseName,movie_Name,movie_Age,movie_Place,movie_Type," +
                "movie_Language,movie_Subtitle,movie_ReleaseDate,movie_IMDbScore,movie_DBScore,movie_Times,movie_Director," +
                "movie_Scriptwriter,movie_Actor,movie_Introduction) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int i = 0;
        try {
            i = queryRunner.update(sql,movies.getMovie_Picture(),movies.getMovie_ChineseName(),movies.getMovie_Name(),movies.getMovie_Age(),
                    movies.getMovie_Place(),movies.getMovie_Type(),movies.getMovie_Language(),movies.getMovie_Subtitle(),movies.getMovie_ReleaseDate(),
                    movies.getIMDbScore(),movies.getDBScore(),movies.getMovie_Times(),movies.getMovie_Director(),
                    movies.getMovie_Scriptwriter(),movies.getMovie_Actor(),movies.getMovie_Introduction());

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return i > 0;
    }
    //删除视频
    @Override
    public int deleteMoviesById(String movie_Id) {
        String sql = "delete from vms.movie where movie_Id=?";
        int i = 0;
        try {
            i = queryRunner.update(sql,movie_Id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    //修改视频信息
    @Override
    public boolean UpdateMoviesById(String movie_Picture, String movie_ChineseName, String movie_Name, String movie_Age,
                                    String movie_Place, String movie_Type, String movie_Language, String movie_Subtitle,
                                    String movie_ReleaseDate, String IMDbScore, String DBScore, String movie_Times,
                                    String movie_Director, String movie_Scriptwriter, String movie_Actor,
                                    String movie_Introduction,String movie_Link,Integer movie_Id) {
        String sql = "update vms.movie set movie_Picture= ?, movie_ChineseName= ?,movie_Name= ?,movie_Age= ?,movie_Place= ?," +
                "movie_Type=?,movie_Language=?,movie_Subtitle=?,movie_ReleaseDate=?,movie_IMDbScore=?," +
                "movie_DBScore=?,movie_Times=?,movie_Director=?,movie_Scriptwriter=?,movie_Actor=?,movie_Introduction=?,movie_Link=?" +
                " where movie_id = ?";
        int i = 0;
        try {
            i = queryRunner.update(sql,movie_Picture,movie_ChineseName,movie_Name,movie_Age,movie_Place,movie_Type,
                    movie_Language,movie_Subtitle,movie_ReleaseDate,IMDbScore,DBScore,movie_Times,movie_Director,
                    movie_Scriptwriter,movie_Actor,movie_Introduction,movie_Link,movie_Id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(i);
        return i > 0;
    }
    //显示视频具体信息
    @Override
    public Movies DisplayMoviesById(String movie_id) {
        String sql = "select movie_Picture,movie_ChineseName,movie_Name,movie_Age,movie_Place,movie_Type," +
                "movie_Language,movie_Subtitle,movie_ReleaseDate,movie_IMDbScore as IMDbScore,movie_DBScore as DBScore," +
                "movie_Times,movie_Director,movie_Scriptwriter,movie_Actor,movie_Introduction, movie_Link from movie where movie_id = ?";
        Movies movies = null;
        try {
            movies = queryRunner.query(sql,new BeanHandler<>(Movies.class),movie_id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return movies;
    }

    /**
     * 展示所有视频信息
     */
    @Override
    public List<Movies> selectAllMovies(Integer currentPage, Integer pageSize) {

        String sql = "select movie_Id, movie_ReleaseTime, movie_Picture, movie_ChineseName, movie_Name, movie_Age, " +
                "movie_Place, movie_Type, movie_Language, movie_Subtitle, movie_ReleaseDate, movie_IMDbScore, " +
                "movie_DBScore, movie_Times, movie_Director, movie_Scriptwriter, movie_Actor, movie_Introduction, " +
                "movie_Award, create_by as createBy, create_time as createTime, update_by as updateBy, " +
                "update_time as updateTime, remark" +
                " from vms.movie  limit ?,?";
        //定义用户列表
        List<Movies> query = null;
        try {
            query = queryRunner.query(sql, new BeanListHandler<>(Movies.class), currentPage, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    /**
     * 得到movie表中的数据行数
     *
     * @return 数据行数
     */
    @Override
    public Long getAllMoviesSize() {
        String sql = "select COUNT(movie_Id) as sum from vms.movie";
        Long sum = 0l;
        try {
            sum = (Long) queryRunner.query(sql, new ScalarHandler("sum"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sum;
    }

    @Override
    public List<Movies> selectMoviesByName(String movie_Name) {
        List<Movies> list = new ArrayList<>();
        //1) 准备SQL语句和参数
        StringBuilder sb = new StringBuilder();
        List<Object> pars = new ArrayList<>();
        // 展示 视频名称,译名，上映时间，导演，编剧，演员
        sb.append("select movie_Name,movie_ChineseName,movie_ReleaseDate,movie_Director,movie_Scriptwriter,movie_Actor from vms.movie where 1=1 ");

        if (movie_Name != null && !"".equals(movie_Name)) {
            sb.append("and movie_Name like ?");
            pars.add("%" + movie_Name + "%");
        }
        try {
            list = queryRunner.query(sb.toString(), new BeanListHandler<>(Movies.class), pars.toArray());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 通过id查询电影详细信息
     *
     * @param id 要查询的电影id
     * @return 电影实体
     */
    @Override
    public Movies getMovieById(Integer id) {
        String sql = "select movie_Id, movie_ReleaseTime, movie_Picture, movie_ChineseName," +
                " movie_Name, movie_Age, movie_Place, movie_Type, movie_Language, movie_Subtitle, " +
                "movie_ReleaseDate, movie_IMDbScore as IMDbScore, movie_DBScore as DBScore, movie_Times, movie_Director, " +
                "movie_Scriptwriter, movie_Actor, movie_Introduction, movie_Award, create_by, " +
                "create_time, update_by, update_time, remark from vms.movie where movie_Id = ?";
        Movies query = null;
        try {
            query = queryRunner.query(sql, new BeanHandler<>(Movies.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

}
